--Testdaten Version 1.0

COMMIT;

SET NAMES 'UTF8';

drop table if exists hibernate_sequence;
drop table if exists TimeInterval;
drop table if exists Employee;
drop table if exists Activity;
drop table if exists Department;



create table Activity (id bigint not null, description varchar(255) not null, primary key (id));
create table Department (id bigint not null, description varchar(255) not null, primary key (id));
create table Employee (id bigint not null, employmentType float not null, firstName varchar(255), isDepartmentManager bit, lastName varchar(255), password varchar(255) not null, staffNumber bigint not null, username varchar(255) not null, department_id bigint, primary key (id));
create table TimeInterval (id bigint not null, comment varchar(255), from_Date datetime not null, to_Date datetime not null, activity_id bigint, employee_id bigint, primary key (id));
alter table Employee add constraint UK_j2ftt6g7a8ohsf079225ldlf9  unique (staffNumber);
alter table Employee add constraint UK_v9p52k4owkd6pgqpiw3r2ovl  unique (username);
alter table Employee add constraint FK4AFD4ACEEB72BF9B foreign key (department_id) references Department (id);
alter table TimeInterval add constraint FK4036D2D24A57065B foreign key (activity_id) references Activity (id);
alter table TimeInterval add constraint FK4036D2D233579EBB foreign key (employee_id) references Employee (id);
create table hibernate_sequence ( next_val bigint );
insert into hibernate_sequence values ( 1 );


BEGIN;

--Department hinzufügen
INSERT INTO Department VALUES(1,'Internettechnik');
INSERT INTO Department VALUES(2,'Industrie-Management');
INSERT INTO Department VALUES(3,'EVU');
 
--Activity erstellen
INSERT INTO Activity VALUES(1,'Projektarbeit');
INSERT INTO Activity VALUES(2,'Forschungsarbeit');
INSERT INTO Activity VALUES(3,'Lehrveranstaltung');

--Employee erstellen   Frage Employment type float??
INSERT INTO Employee VALUES (1,0.25,'Hannes',1,'Schwaiger',MD5('hannes'),121551667,'hschwaiger',2);
INSERT INTO Employee VALUES (2,1,'Thorsten',0,'Jager',MD5('thorsten'),121441556,'tjager',2);

INSERT INTO Employee VALUES (3,0.75,'Michael',1,'Kobel',MD5('michael'),131221334,'mkobel',1);
INSERT INTO Employee VALUES (4,0.5,'Josef',0,'Heinel',MD5('josef'),131112555,'jheinel',1);

INSERT INTO Employee VALUES (5,0.5,'Matthias',1,'Kunzer',MD5('matthias'),141521637,'mkunzer',3);
INSERT INTO Employee VALUES (6,1.0,'Florian',0,'Siegel',MD5('florian'),141421225,'fsiegel',3);


--TimeInterval erstellen

--weniger 6 Stunden
INSERT INTO TimeInterval VALUES (9001,'comment','2014-11-24 08:00:00','2014-11-24 12:00:00',1,2);
--mehr als 6 Stunden (30min Abzug)
INSERT INTO TimeInterval VALUES (9002,'comment','2014-11-25 08:00:00','2014-11-25 16:30:00',2,3);
--mehr als 10 Stunden
INSERT INTO TimeInterval VALUES (9003,'comment','2014-11-25 08:00:00','2014-11-25 19:00:00',1,1);
--Freitag 20:00 - 22:00
INSERT INTO TimeInterval VALUES (9004,'comment','2014-11-28 20:00:00','2014-11-28 22:00:00',3,4);
--Samstag
INSERT INTO TimeInterval VALUES (9005,'comment','2014-11-29 08:00:00','2014-11-29 13:30:00',3,5);

--TimeInterval Ergänzungen
INSERT INTO TimeInterval VALUES (9006,'comment','2014-11-26 08:00:00','2014-11-26 19:00:00',1,1);
INSERT INTO TimeInterval VALUES (9007,'comment','2014-11-27 08:00:00','2014-11-27 16:00:00',2,1);

INSERT INTO TimeInterval VALUES (9008,'comment','2014-11-25 08:00:00','2014-11-25 11:00:00',1,2);
INSERT INTO TimeInterval VALUES (9009,'comment','2014-11-26 08:00:00','2014-11-26 17:00:00',2,2);

INSERT INTO TimeInterval VALUES (9010,'comment','2014-11-26 08:00:00','2014-11-26 12:30:00',1,3);
INSERT INTO TimeInterval VALUES (9011,'comment','2014-11-27 08:00:00','2014-11-27 11:30:00',3,3);

INSERT INTO TimeInterval VALUES (9012,'comment','2014-11-27 09:00:00','2014-11-27 18:00:00',2,4);
INSERT INTO TimeInterval VALUES (9013,'comment','2014-11-26 10:00:00','2014-11-26 14:00:00',3,4);

INSERT INTO TimeInterval VALUES (9014,'comment','2014-11-25 08:00:00','2014-11-25 16:30:00',2,5);
INSERT INTO TimeInterval VALUES (9015,'comment','2014-11-26 08:00:00','2014-11-26 13:30:00',2,5);

INSERT INTO TimeInterval VALUES (9016,'comment','2014-11-25 08:00:00','2014-11-25 18:30:00',3,6);
INSERT INTO TimeInterval VALUES (9017,'comment','2014-11-27 08:00:00','2014-11-27 13:30:00',1,6);



--Urlaub - 8 Stunden
--INSERT INTO TimeInterval VALUES (5,'Urlaub',2014-11-24 08:00:00,2014-11-24 16:30:00,2,2);
--Krankenstand - 8 Stunden
--INSERT INTO TimeInterval VALUES (6,'Krankenstand',2014-11-24 08:00:00,2014-11-24 16:30:00,1,4);
--Feiertag - 0 Stunden
--INSERT INTO TimeInterval VALUES (6,'Feiertag',2014-11-24 08:00:00,2014-11-24 11:30:00,3,5);
